package com.partner.dao;

import io.rong.models.FormatType;
import io.rong.models.SdkHttpResult;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.partner.domain.Friend;
import com.partner.domain.FriendRelation;
import com.partner.domain.GroupUser;
import com.partner.domain.MyFriend;
import com.partner.domain.MyTask;
import com.partner.domain.User;
import com.partner.domain.UserAndGroup;
import com.partner.domain.Yaoqingfriend;
import com.partner.response.LoginResponse;
import com.partner.util.ApiHttpClient;
import com.partner.util.CommonData;
import com.partner.util.DBUtil;

/**
 * 用户dao
 * @author Jinshuan
 *
 */
public class UserDao {
	QueryRunner queryRunner_auto = new QueryRunner(DBUtil.getDataSource());//由它自己关闭
	QueryRunner queryRunner_self = new QueryRunner();//由我们开启由我们关闭
	
	//新增用户
	public int insertUser(User user){
			String sql = "insert into user values(?,?,?,?,?,?,?,?,?)";
			Object[] params = {user.getPhone(),user.getUsername(),user.getPassword(),user.getPhoto(),user.getYear(),
					user.getZhuanye(),user.getSchool(),user.getBindphone(),user.getToken()};
			int result = 1;//1是表示普通的失败。2是表示手机号码已经注册过了。0是表示成功
			try {
				if( queryRunner_auto.update(sql, params)>0){
					result = 0;
				}
			} catch (SQLException e) {
				//如果注册失败，再看看是不是因为此号码已经注册过
				if(user.getPhone()!=null){
					if(duplicatePhone(user.getPhone())){
						result = 2;
						System.out.println("手机号已经注册过了");
					}else{
						result = 1;
				}
				}
			}
			
			return result;
	}
	
	//查看对应的号码是不是已经存在了
	public boolean duplicatePhone(String phone){
		String sql = "select * from user where phone=?";
		Object[] params = {phone};
		boolean result = false;
		try {
			if(queryRunner_auto.query(sql, new BeanListHandler<User>(User.class),params)!=null&&
					queryRunner_auto.query(sql, new BeanListHandler<User>(User.class),params).size()>0){
				result = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			
		}
		
		return result;
	}
	
	//登录dao
	public LoginResponse login(String phone,String password){
		LoginResponse loginResponse = new LoginResponse();
		String sql = "select * from user where phone=? and password=?";
		Object[] params = {phone,password};
//		int result = 1;//1表示普通的用户名不存在失败，0是成功，2是用户名不存在,3是密码错误
		try {
			if(queryRunner_auto.query(sql, new BeanListHandler<User>(User.class),params)!=null&&
					queryRunner_auto.query(sql, new BeanListHandler<User>(User.class),params).size()>0){
				User user = queryRunner_auto.query(sql, new BeanListHandler<User>(User.class),params).get(0);
				System.out.println("dao里面的--"+user.toString());
				loginResponse.setUser(user);
				loginResponse.setCode("2000");
//				result = 0;
			}//如果失败看是不是用户名不存在
			else if(!duplicatePhone(phone)){
				loginResponse.setCode("400");
				loginResponse.setDescription("用户名不存在");
//				result = 2;
			}else{
				//看是不是密码错误
				String sql1 = "select * from user where  phone=? and password != ?";
				Object[] params1 = {phone,password};
				if(queryRunner_auto.query(sql1, new BeanListHandler<User>(User.class),params1)!=null&&
						queryRunner_auto.query(sql1, new BeanListHandler<User>(User.class),params1).size()>0){
//					result = 3;
					loginResponse.setCode("400");
					loginResponse.setDescription("密码错误");
				}
				
			}
		} catch (SQLException e) {
			loginResponse.setCode("400");
			loginResponse.setDescription("登录失败。稍后再试");
//			result = 1;
		}
		
		return loginResponse;
	}
	
	//修改用户信息
	public boolean updateUser(User user){
		String sql = "update user set zhuanye = ?,school = ?,year = ?,username = ?,bindphone = ?,photo=? where phone=?";
		Object[] params = {user.getZhuanye(),user.getSchool(),user.getYear(),user.getUsername(),user.getBindphone(),user.getPhoto(),user.getPhone()};
		try {
			if(queryRunner_auto.update(sql, params)>0){
				System.out.println("更新用户信息成功");
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	//得到对应用户的联系人列表
	public List<MyFriend> getMyFriends(String uid){
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String sql = "select GROUP_CONCAT(fid) as phones,GROUP_CONCAT(state) as states,GROUP_CONCAT(nickname) as nicknames,groupname,COUNT(fid) as num from friend where uid = ? and (state=0 or state=3) GROUP BY groupname";
		Object params[] = {uid};
		List<MyFriend> myFriends = null;
		try {
			myFriends = queryRunner_self.query(conn, sql, new BeanListHandler<MyFriend>(MyFriend.class), params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(myFriends!=null){
			
			//如果查询出来里面有分组，则循环遍历每一个分组的人
			for(MyFriend myFriend:myFriends){
				List<Friend> friends = new ArrayList<Friend>();
				//每个组的电话，状态，以及昵称都用逗号分隔的字符串得到
				String phones[] = myFriend.getPhones().split(",");
				String states[] = myFriend.getStates().split(",");
				String nicknames[] = myFriend.getNicknames().split(",");
				System.out.println(phones.length+"--"+nicknames.length+"---"+myFriend.getNicknames());
				String sql2 = "select phone as userId,username as userName,school,zhuanye,year,photo from user where phone = ?";
			//循环遍历每个分组里面用户id，将每个用户具体得查询出来
				for(int i=0;i<phones.length;i++){
					Object params2[] = {phones[i]};
					try {
						Friend friend = queryRunner_self.query(conn, sql2, new BeanHandler<Friend>(Friend.class) , params2);
						if(friend!=null){
							//将每个用户的在线状态也得到
							friend.setOnline(ApiHttpClient.checkOnline(CommonData.app_key, CommonData.app_secret, phones[i], FormatType.json));
							friend.setNickname(nicknames[i]);
							friends.add(friend);
						}
						friend.setState(states[i]);
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				myFriend.setFriends(friends);
			}
		}
		
		//提交
		DbUtils.commitAndCloseQuietly(conn);
		return myFriends;
	}
	
	//查询所有非好友
	public List<User> getNotFriend(String phone,String condition){
		String sql = "select * from user where phone not in (select fid from friend where uid = ? ) and phone!=? "+
"and (phone like ? or username like ?)";
		Object[] params = {phone,phone,"%"+condition+"%","%"+condition+"%"};
		try {
			List<User> users = queryRunner_auto.query(sql, new BeanListHandler<User>(User.class),params);
			System.out.println("--"+phone+condition+users);
			return users;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	//搜索对应的群
	public List<UserAndGroup> getNotGroups(String phone,String condition){
		String sql = "select groupId,groupName,photo,count(groupId) as groupUserNum  from groups where groupId not in(select groupId from groups where userid = ?) and (groupId like ? or groupName like ?) GROUP BY groupId";
//		String sql = "select groupId,groupName,photo from groups where groupName like ? OR groupId like ? and userid != ? GROUP BY groupId";
		Object[] params = {phone,"%"+condition+"%","%"+condition+"%"};
		try {
			List<UserAndGroup> userAndGroups = queryRunner_auto.query(sql, new BeanListHandler<UserAndGroup>(UserAndGroup.class),params);
			System.out.println(userAndGroups);
			return userAndGroups;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
			
	}
	
	//加入群
	public boolean joinGroup(String userId,String groupId,String groupName,String nickName){
		try {
			SdkHttpResult result = 	ApiHttpClient.joinGroup(CommonData.app_key, CommonData.app_secret, userId, groupId, groupName, FormatType.json);
			//说明加入融云的群成功
			if(result.getHttpCode()==200){
				//然后往本地数据库插入数据
				GroupUser groupUser = groupPhotoAndQunzhu(groupId);
				if(groupUser!=null){
					String sql = "insert into groups VALUES (?,?,?,?,?,?)";
					Object[] params = {groupId,groupName,userId,groupUser.getImg(),groupUser.getQunzhu(),nickName};
					if(queryRunner_auto.update(sql, params)>0){
						return true;
					}
				}
			}else{
				return false;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	//通过群id来找出这个群的群主和群头像
	public GroupUser groupPhotoAndQunzhu(String groupId){
		String sql = "select * FROM groups where groupId = ? GROUP BY groupId";
		Object[] params = {groupId};
		try {
			GroupUser groupUser = (GroupUser) queryRunner_auto.query(sql, new BeanHandler<GroupUser>(GroupUser.class), params);
			return groupUser;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
		
	}
	//单方面添加好友
//	public boolean addFriend(String from,String to,String groupname,String nickname){
//		Connection conn = null;
//		try {
//			conn = DBUtil.getConnection();
//			conn.setAutoCommit(false);
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//	System.out.println(from+"--"+to+"--"+groupname+"--"+nickname);
//			String sql =  "insert into friend values (?,?,?,?,?)";
//			String sql1 = "select * from user";
//			Object[] params = {from,to,groupname,"1",nickname};
//			Object[] params2 = {to,from,"未分组","2"," "};
//			try {
////				if(queryRunner_self.query(conn, sql1, new BeanListHandler<User>(User.class))!=null){
////					if(queryRunner_self.update(conn, sql, params)>0){
////						return true;
////					}
////				}
//				if(queryRunner_self.update(conn, sql, params)>0){
//					if(queryRunner_self.update(conn, sql, params2)>0){
//						
//						return true;
//					}
//				}
//				DbUtils.commitAndCloseQuietly(conn);
//			} catch (SQLException e) {
//				e.printStackTrace();
//			}
//		
//		return false;
//		
//	}
	
	//单方面添加好友
		public boolean addFriend(String from,String to,String groupname,String nickname){
			//增加到回复表中
			String sql = "insert into friend values (?,?,?,?,?),(?,?,?,?,?)";
			
			Object[] params = {from,to,groupname,"1",nickname,to,from,"未分组","2"," "};
			System.out.println(from+"--"+to+"--"+groupname+"--"+nickname);
			
			try {
				int re = queryRunner_auto.update(sql, params);
				if(re>0){
					return true;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return false;
		}
		
	
	//同意好友加我
	public boolean agreeFriend(String from,String to,String nickname,String groupname){
//		Connection conn = null;
//		try {
//			conn = DBUtil.getConnection();
//			conn.setAutoCommit(false);
//		} catch (SQLException e1) {
//			e1.printStackTrace();
//		}
		String sql  = "update friend set state = 0,nickname = ?,groupname = ? where uid = ? and fid = ?";
		String sql2 = "update friend set state = 0 where fid = ? and uid = ? ";
		Object[] params2 = {from,to};
		Object[] params = {nickname,groupname,from,to};
		try {
			if(queryRunner_auto.update(sql, params)>0){
				if(queryRunner_auto.update(sql2, params2)>0){
					return true;
				}
			}
//			DbUtils.commitAndCloseQuietly(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return false;
		
	}
	
	//是否有好友加我
	public List<User> getFriendNotice(String phone){
		String sql = "select * from user where phone in(select fid from friend where uid = ? and state = '2')";
		Object[] params = {phone};
		try {
			List<User> users = queryRunner_auto.query(sql, new BeanListHandler<User>(User.class), params);
			return users;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	//是否有群加我
	public List<Yaoqingfriend> getGroupNotice(String phone){
		String sql = "select DISTINCT yaoqingfriend.*,groups.groupName,groups.photo,groups.qunzhu,`user`.username from yaoqingfriend,groups,`user`"
				+ "where groups.groupId=yaoqingfriend.groupId and `user`.phone=yaoqingfriend.from_who and yaoqingfriend.to_who=?";
		Object[] params = {phone};
		
		List<Yaoqingfriend> yaoqingfriends = null ;
		try {
			yaoqingfriends = queryRunner_auto.query(sql, new BeanListHandler<Yaoqingfriend>(Yaoqingfriend.class), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if(yaoqingfriends==null){
			yaoqingfriends = new ArrayList<Yaoqingfriend>();
		}
		
		//如果是我的好友并且有昵称的就给昵称赋值
		List<FriendRelation> friendRelations = getMyFriends2(phone);
		for(Yaoqingfriend yaoqingfriend:yaoqingfriends){
			for(FriendRelation friendRelation:friendRelations){
				if(friendRelation.getFid().equals(yaoqingfriend.getFrom_who())){
					yaoqingfriend.setNickname(friendRelation.getNickname());
				}
			}
		}
		return yaoqingfriends;
	}
	
	//查询我的好友
	public List<FriendRelation> getMyFriends2(String phone){
		String sql = "select * from friend where uid  = ?";
		Object[] params = {phone};
		List<FriendRelation> friendRelations = null;
		try {
			friendRelations = queryRunner_auto.query(sql, new BeanListHandler<FriendRelation>(FriendRelation.class),params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
				if(friendRelations==null){
					friendRelations = new ArrayList<FriendRelation>();
				}
				return friendRelations;
	}
	//是否有好友邀请我加入群
//	public String getYaoQingIntoGroup(String uid){
//		String result = "";
//		//得到是谁邀请我
//		String sql = "select * from user where phone in(select fid from friend where uid = ? and state = '6')";
//		Object[] params = {uid};
////		List
//				 
//	}
	
	//如果明确是拒绝
	public boolean declineAddFriend(String from,String to){
		//如果是拒绝的话就将两条对应的记录给删除
		String sql = "delete from friend where (uid = ? and fid = ?) or (fid = ? and uid = ?)";
		Object[] params = {from,to,from,to};
		try {
			if(queryRunner_auto.update(sql, params)>1){
				System.out.println("拒绝true--");
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return false;
	}
	
	//去查询我的任务
	public List<MyTask> selectMyTask(String phone,int pageNow,int pageSize){
		String sql = "select * from task where phone=? order by end_time desc limit ?,? ";
		Object[] params = {phone,(pageNow-1)*pageSize,pageSize};
		
		List<MyTask> myTasks = null;
		try {
			myTasks = queryRunner_auto.query(sql, new BeanListHandler<MyTask>(MyTask.class), params)
;			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			myTasks = new ArrayList<MyTask>();
			return myTasks;
		}
		if(myTasks==null){
			myTasks = new ArrayList<MyTask>();
		}
		return myTasks;
	}
	
	//删除其中一个任务
	public boolean deleteMyTask(String phone,String create_time){
		String sql = "DELETE from  task where create_time = ? and phone = ?";
		Object[] params = {create_time,phone};
		
		boolean result = false;
		try {
			if(queryRunner_auto.update(sql, params)>0){
				result = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return result;
	}
	
	//增加我的任务
	public boolean addTask(String task,String start_time ,String end_time,String questcode,String create_time,String phone){
		String sql = "insert into task value(?,?,?,?,?,?,?)";
		Object[] params = {task,1,start_time,end_time,create_time,phone,questcode};
		boolean result = false;
		try {
			if(queryRunner_auto.update(sql,params)>0){
				result = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return result;
	}
	
	//更改任务的状态
	public boolean updateTask(String state ,String phone,String create_time){
		String sql = "update task set state = ? where phone=? and create_time = ?";
		int state1 = Integer.parseInt(state);
		Object[] params = {state1,phone,create_time};
		boolean result = false;
		try {
			if(queryRunner_auto.update(sql, params)>0){
				result = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		System.out.println(result);
	return result;
				
	}
	
	//邀请好友加入对应的群。这时要得到不是这个群的好友
	public List<MyFriend> getMyFriendsNotInGroup(String uid,String groupId){
		Connection conn = null;
		try {
			conn = DBUtil.getConnection();
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String sql = "select GROUP_CONCAT(fid) as phones,GROUP_CONCAT(state) as states,GROUP_CONCAT(nickname) as nicknames,groupname,COUNT(fid) as num from friend  where friend.fid not in(select userid FROM groups where groupId=?) and uid = ? and (state=0 or state=3)  GROUP BY groupname";
		Object params[] = {groupId,uid};
		List<MyFriend> myFriends = null;
		try {
			myFriends = queryRunner_self.query(conn, sql, new BeanListHandler<MyFriend>(MyFriend.class), params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		if(myFriends!=null){
			
			//如果查询出来里面有分组，则循环遍历每一个分组的人
			for(MyFriend myFriend:myFriends){
				List<Friend> friends = new ArrayList<Friend>();
				//每个组的电话，状态，以及昵称都用逗号分隔的字符串得到
				String phones[] = myFriend.getPhones().split(",");
				String states[] = myFriend.getStates().split(",");
				String nicknames[] = myFriend.getNicknames().split(",");
				System.out.println(phones.length+"--"+nicknames.length+"---"+myFriend.getNicknames());
				String sql2 = "select phone as userId,username as userName,school,zhuanye,year,photo from user where phone = ?";
			//循环遍历每个分组里面用户id，将每个用户具体得查询出来
				for(int i=0;i<phones.length;i++){
					Object params2[] = {phones[i]};
					try {
						Friend friend = queryRunner_self.query(conn, sql2, new BeanHandler<Friend>(Friend.class) , params2);
						if(friend!=null){
							//将每个用户的在线状态也得到
							friend.setOnline(ApiHttpClient.checkOnline(CommonData.app_key, CommonData.app_secret, phones[i], FormatType.json));
							friend.setNickname(nicknames[i]);
							friends.add(friend);
						}
						friend.setState(states[i]);
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				myFriend.setFriends(friends);
			}
		}
		
		//提交
		DbUtils.commitAndCloseQuietly(conn);
		return myFriends;
	}
	//修改密码
	public int updatePassword(String phone,String oldPassword,String newPassword){
		String sql = "update user set password = ? where phone = ? and password = ?";
		Object[] params = {newPassword,phone,oldPassword};
		try {
			int result = queryRunner_auto.update(sql, params);
			if(result==0){
				//说明密码错误，因为一般不会账号在数据库里找不到吧
				return 2;
			}else if(result>0){
				return  1;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
		return 0;
	}
	//将绑定的手机号给找回来
	public String findBindPhone(String phone){
		String sql = "select bindphone from user where phone = ? ";
		Object[] params = {phone};
		String bindphone = "";
		try {
			if(duplicatePhone(phone)){
				bindphone = (String) queryRunner_auto.query(sql, new ScalarHandler(), params);
			 if(bindphone==null){
				 bindphone = "1";//m没有绑定手机号
			 }
			}else{
				bindphone = "0";//没有对应的账号
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(bindphone);
		return bindphone;
	}
	//忘记密码的时候修改密码
			public  boolean updatePwd(String phone,String newPassword){
				String sql = "update user set password = ? where phone = ?";
				Object[] params = {newPassword,phone};
				try {
					if(queryRunner_auto.update(sql, params)>0){
						return true;
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
				return false;
			}
			//修改用户头像
			public boolean updateUserImgIcon(String phone,String img){
				String sql = "update user set photo=? where phone = ?";
				Object[] params = {img,phone};
				try {
					if(queryRunner_auto.update(sql,params)>0){
						return true;
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return false;
			}
}
